﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace SqlServer
{
    public partial class Form4 : Form
    {
        private Form1 form1;
        private SqlConnection connection;
        public string name;
        public string connectionString = "Data Source=localhost;Initial Catalog=C#sql;User ID=C#test;Password=123";
        
        public Form4(Form1 form1,string dataname)
        {
            InitializeComponent();
            this.form1 = form1;
            name = dataname;
            connection = new SqlConnection(connectionString);
        }

        private void button5_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void button1_Click(object sender, EventArgs e) //添加数据
        {
            string[] text = { textBox1.Text, textBox2.Text, textBox3.Text, textBox4.Text, textBox5.Text,textBox6.Text };
            string sql = "INSERT INTO {0} (工号,姓名,年龄,性别,电话,部门) VALUES ('{1}','{2}','{3}','{4}','{5}','{6}')";
            sql = string.Format(sql, name, text[0].Trim(), text[1].Trim(), text[2].Trim(), text[3].Trim(), text[4].Trim(), text[5].Trim());
            using (SqlCommand command = new SqlCommand(sql, connection))
            {
                try
                {
                    connection.Open();
                    int result = command.ExecuteNonQuery();
                    if (result > 0)
                    {
                        MessageBox.Show("插入数据成功！", "系统信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    }
                }
                catch (SqlException ex)
                {
                    if (ex.Number == 2627)
                    {
                        MessageBox.Show("重复数据！请重新输入！");
                        textBox1.Clear();
                        textBox2.Clear();
                        textBox3.Clear();
                        textBox4.Clear();
                        textBox5.Clear();
                        textBox6.Clear();
                    }
                    else
                    {
                        MessageBox.Show("插入数据失败，具体为:" + ex.Message, "系统信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    }
                }
                finally
                {
                    form1.RefreshData();
                    connection.Close();
                }
            }
        }

        private void button2_Click(object sender, EventArgs e) //修改数据
        {
            string sql = "update {0} set ";
            sql = string.Format(sql, name);
            if (!string.IsNullOrWhiteSpace(textBox12.Text.Trim())) sql += "工号 ='" + textBox12.Text.Trim() + "',";
            if (!string.IsNullOrWhiteSpace(textBox11.Text.Trim())) sql += "姓名 ='" + textBox11.Text.Trim() + "',";
            if (!string.IsNullOrWhiteSpace(textBox10.Text.Trim())) sql += "年龄 ='" + textBox10.Text.Trim() + "',";
            if (!string.IsNullOrWhiteSpace(textBox9.Text.Trim())) sql += "性别 ='" + textBox9.Text.Trim() + "',";
            if (!string.IsNullOrWhiteSpace(textBox8.Text.Trim())) sql += "电话 ='" + textBox8.Text.Trim() + "',";
            if (!string.IsNullOrWhiteSpace(textBox7.Text.Trim())) sql += "部门 ='" + textBox7.Text.Trim() + "',";
            sql = sql.Substring(0, sql.Length - 1);
            sql = sql + " where 工号 ='" + textBox12.Text + "'";
            using (SqlCommand command = new SqlCommand(sql, connection))
            {
                try
                {
                    connection.Open();
                    int result = command.ExecuteNonQuery();
                    if (result > 0)
                    {
                        MessageBox.Show("更新数据成功！", "系统信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    }
                    else MessageBox.Show("没有找到数据！", "系统信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                catch (Exception ex)
                {
                    MessageBox.Show("更新数据失败，具体为:" + ex.Message, "系统信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                finally
                {
                    form1.RefreshData();
                    connection.Close();
                }

            }
        }

        private void button3_Click(object sender, EventArgs e) //查询数据
        {
            DataTable dataTable = new DataTable();
            string sql = "SELECT * FROM {0} where 工号 = '{1}'";
            sql = string.Format(sql, name,textBox13.Text.Trim());
            Form3 form3 = new Form3(sql); //传输sql命令
            form3.Show();

        }
        private void button4_Click(object sender, EventArgs e) //删除数据
        {
            if (string.IsNullOrWhiteSpace(textBox14.Text.Trim()))
            {
                MessageBox.Show("请输入要删除信数据的工号", "系统信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }
            string sql = "delete from {0} where 工号 = '{1}'";
            sql = string.Format(sql, name, textBox14.Text.Trim());
            using (SqlCommand command = new SqlCommand(sql, connection))
            {
                try
                {
                    connection.Open();
                    int result = command.ExecuteNonQuery();
                    if (result > 0)
                    {
                        MessageBox.Show("删除数据成功！", "系统信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    }
                    else MessageBox.Show("没有找到数据！", "系统信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                catch (Exception ex)
                {
                    MessageBox.Show("删除数据失败，具体为;" + ex.Message, "系统信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                finally
                {
                    form1.RefreshData();
                    connection.Close();
                }
            }
        }
    }
}
